﻿using System;
using System.Linq;
using System.Text;
using Kaidou.Model;
using System.Data;
using System.Data.SqlClient;
using Kaidou.Util;
using System.Collections;

namespace Kaidou.Persister
{
    public class TRN_Purch_SealPersister : Common
    {
        /**
	    * intで受け取ったシール番号をStringに戻す
	    * decimal撲滅のため
	    */
	    private String getStrSealNo(int sealNo) {
		    string strSealNo = sealNo.ToString();
		    if (strSealNo.Length < 9) {
			    strSealNo = "0" + strSealNo;
		    }
		    return strSealNo;
	    }

        /**
	     * 指定された確認シール番号のデータモデルを取得する。
	     * @param con コネクション
	     * @param seal_no_start 確認シールstart番号
	     * @param seal_no_end 確認シールend番号
	     * @return boolean
	     */
	    public bool existSealNo(int seal_no_start, int seal_no_end) {

		    bool flag = false;

		    string sql = null;
            SqlDB db = new SqlDB();
            try
            {


                SqlParameter[] sqlParameter = { };

                if (seal_no_end > 0)
                {
                    sql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no between '" + getStrSealNo(seal_no_start) + "' and  '" + getStrSealNo(seal_no_end) + "' ";
                }
                else
                {
                    sql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no = '" + getStrSealNo(seal_no_start) + "'";
                }

                LogManager.info("TRN_Purch_SealPersister sql : " + sql);

                SqlDataReader dataReader = db.GetDataCmd(sql, sqlParameter);
                int count = 0;

                if (dataReader.Read())
                {
                    count = (int)dataReader[0];
                }


                if (seal_no_end > 0)
                {
                    if (count == (seal_no_end - seal_no_start + 1))
                    {
                        flag = true;
                    }
                }
                else
                {
                    if (count == 1)
                    {
                        flag = true;
                    }
                }

            }
            catch (Exception es)
            {
                LogManager.error("TRN_Purch_SealPersister existSealNo " + es.Message);
            }
            finally
            {
                db.Close();
            }

            return flag;

	    }

        /**
	     * 指定された確認シール番号が破棄されていないことを確認する
	     * @param con コネクション
	     * @param seal_no_start 確認シールstart番号
	     * @param seal_no_end 確認シールend番号
	     * @return int 範囲内で破棄されたシールの数
	     */
	    public int countCanselSealNo(string seal_no_start, string seal_no_end) {
            LogManager.info("TRN_Purch_SealPersister countCanselSealNo in");
		    int count = 0;
            string pssql = null;
            LogManager.info(" TRN_Purch_SealPersister seal_no_start : " + seal_no_start);
            LogManager.info(" TRN_Purch_SealPersister seal_no_end : " + seal_no_end);
            SqlDB db = new SqlDB();
            try
            {
                if (int.Parse(seal_no_end) > 0)
                {
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no between '" + getStrSealNo(int.Parse(seal_no_start)) + "' and '" + getStrSealNo(int.Parse(seal_no_end)) + "' " + " and purch_sts = '" + TrnPurchSeal.STATUS_CANSEL + "'";

                }
                else
                {
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no = '" + getStrSealNo(int.Parse(seal_no_start)) + "' " + " and purch_sts = '" + TrnPurchSeal.STATUS_CANSEL + "' ";

                }

                LogManager.info("TRN_Purch_SealPersister existCanselSealNo - sql " + pssql);

                SqlParameter[] sqlParameter = { };

                SqlDataReader dataReader = db.GetDataCmd(pssql, sqlParameter);

                if (dataReader.Read())
                {
                    count = (int)dataReader[0];
                }
                LogManager.info("TRN_Purch_SealPersister countCanselSealNo count " + count);

                dataReader.Close();

            }
            catch (Exception ex)
            {
                LogManager.error("TRN_Purch_SealPersister existCanselSealNo " + ex.Message);
            }
            finally
            {
                db.Close();
            }
            LogManager.info("TRN_Purch_SealPersister countCanselSealNo out ");

		    return count;

	    }


        public int countCanselSealNo2(string seal_no_start, string seal_no_end)
        {
            LogManager.info("TRN_Purch_SealPersister countCanselSealNo in");
            int count = 0;
            string pssql = null;
            LogManager.info(" TRN_Purch_SealPersister seal_no_start : " + seal_no_start);
            LogManager.info(" TRN_Purch_SealPersister seal_no_end : " + seal_no_end);
            SqlDB db = new SqlDB();
            try
            {
                if (int.Parse(seal_no_end) > 0)
                {
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no between '" + getStrSealNo(int.Parse(seal_no_start)) + "' and '" + getStrSealNo(int.Parse(seal_no_end)) + "' " + " and purch_sts != '" + TrnPurchSeal.STATUS_CANSEL + "'";

                }
                else
                {
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no = '" + getStrSealNo(int.Parse(seal_no_start)) + "' " + " and purch_sts != '" + TrnPurchSeal.STATUS_CANSEL + "' ";

                }

                LogManager.info("TRN_Purch_SealPersister existCanselSealNo - sql " + pssql);

                SqlParameter[] sqlParameter = { };

                SqlDataReader dataReader = db.GetDataCmd(pssql, sqlParameter);

                if (dataReader.Read())
                {
                    count = (int)dataReader[0];
                }
                LogManager.info("TRN_Purch_SealPersister countCanselSealNo count " + count);



            }
            catch (Exception ex)
            {
                LogManager.error("TRN_Purch_SealPersister existCanselSealNo " + ex.Message);
            }
            finally
            {
                db.Close();
            }
            LogManager.info("TRN_Purch_SealPersister countCanselSealNo out ");

            return count;

        }

        /**
	     * シール番号が既に登録されているかどうかをチェック。
	     * @param con コネクション
	     * @param seal_no_start 確認シールstart番号
	     * @param seal_no_end 確認シールend番号
	     * @return int 報告件数が0ではないシール番号を含めていれば1を返す。
	     * @throws PersisterSQLException
	     */
	    public int countDuplicationReport( int seal_no_start, int seal_no_end)  {

		    int count = 0;
		    string pssql = null;
            SqlDB db = new SqlDB();
            try
            {

                if (seal_no_end > 0)
                {
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " report_qty != 0 " + " and seal_no between '" + getStrSealNo(seal_no_start) + "' and '" + getStrSealNo(seal_no_end) + "' ";

                }
                else
                {

                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " report_qty != 0 " + " and seal_no = '" + getStrSealNo(seal_no_start) + "' ";

                }
                LogManager.info("TRN_Purch_SealPersister pssql " + pssql);


                SqlParameter[] sqlParameter = { };

                SqlDataReader dataReader = db.GetDataCmd(pssql, sqlParameter);

                if (dataReader.Read())
                {
                    count = (int)dataReader[0];
                }



                LogManager.info("TRN_Purch_SealPersister ### count : " + count);

            }
            catch (Exception e)
            {
                LogManager.error("TRN_Purch_SealPersister countDuplicationReport " + e.Message);
            }
            finally
            {
                db.Close();
            }

		    return count;

	    }

	    /**
	     * シールの購入者と報告者が異なるかどうかをチェック。<br>
	     * 渡されたシール番号について他の組合員コードが存在すると購入者と報告者が異なると判断。
	     * @param con コネクション
	     * @param seal_no_start 確認シールstart番号
	     * @param seal_no_end 確認シールend番号
	     * @param union_code 組合員コード
	     * @return int 渡された条件による組合員コードの数
	     */
	    public int countDifferentUnion(int seal_no_start, int seal_no_end, string union_code) {

		   
		    int count = 0;
		    string pssql = null;
            SqlDB db = new SqlDB();
            try
            {

                if (seal_no_end > 0)
                {
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no between '" + getStrSealNo(seal_no_start) + "' and '" + getStrSealNo(seal_no_end) + "' " + " and union_code != '" + union_code + "'";

                }
                else
                {

                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no = '" + getStrSealNo(seal_no_start) + "' " + " and union_code != '" + union_code + "'";

                }

                LogManager.info("TRN_Purch_SealPersister ### pssql : " + pssql);


                SqlParameter[] sqlParameter = { };

                SqlDataReader dataReader = db.GetDataCmd(pssql, sqlParameter);

                if (dataReader.Read())
                {
                    count = (int)dataReader[0];
                }



                LogManager.info("TRN_Purch_SealPersister ### count : " + count);

            }
            catch (Exception e)
            {
                LogManager.error("TRN_Purch_SealPersister countDifferentUnion " + e.Message);
            }
            finally
            {
                db.Close();
            }
		    return count;
	    }

        public bool insertByProcedure(SqlDB con, TrnJuchud model, int start_no, int end_no)
        {

            LogManager.debug("TRN_Purch_SealPersister - insertByProcedure in --------------------------- ");
            LogManager.debug("TRN_Purch_SealPersister - start_no=" + start_no + " end_no=" + end_no);

            String procName = "Insert_trn_purch_seal_proc";
            // String sql = "EXEC " + procName + "  @p1, @p2, @p3, @p4, @p5, @p6 ";

            try
            {
                SqlParameter[] parmsU = new SqlParameter[] 
            {
                  new SqlParameter("@in_juchu_no", SqlDbType.VarChar,10), //
                  new SqlParameter("@in_juchu_rec_no", SqlDbType.Int,3),//
                  new SqlParameter("@in_start_no", SqlDbType.VarChar,9),//
                  new SqlParameter("@in_end_no", SqlDbType.VarChar,9), //
                  new SqlParameter("@out_sqlcode", SqlDbType.Int),//
                  new SqlParameter("@out_message", SqlDbType.VarChar,200)//
            };
                parmsU[0].Value = model.Id.JuchuNo;
                parmsU[1].Value = model.Id.JuchuRecNo;
                parmsU[2].Value = start_no;
                parmsU[3].Value = end_no;
                parmsU[4].Value = 0;
                parmsU[4].Direction = ParameterDirection.Output;
                parmsU[5].Value = 0;
                parmsU[5].Direction = ParameterDirection.Output;

                con.ExecuteProcedure(procName, ref parmsU);



                int outErrorCode = DBUnit.GetInt32(parmsU[4].Value);
                String outErrorMsg = DBUnit.GetString(parmsU[5].Value);

                LogManager.debug("TRN_Purch_SealPersister - From Insert_trn_purch_seal_proc back sqlCode=" + outErrorCode + " returnString = [" + outErrorMsg + "]");
                if (outErrorCode != 0)
                {
                    LogManager.fatal("TRN_Purch_SealPersister - insert - CallableStatement close sql_code = " + outErrorCode + " returnString = [" + outErrorMsg + "]");
                    throw new Exception(" Insert_trn_purch_seal_proc error ");
                }

                LogManager.debug("TRN_Purch_SealPersister - insertByProcedure out --------------------------- ");
            }
            catch (Exception e)
            {
                throw e;
            }
            //finally
            //{
            //try
            //{
            //    con.Close();
            //}
            //catch (Exception e)
            //{
            //    LogManager.fatal("TRN_Purch_SealPersister - insert - CallableStatement close " + e.ToString());
            //}
            //}
            return true;
        }

        /*
        public bool insertByProcedure(SqlDB db, TrnJuchud model, int start_no, int end_no) 
        {
            try
            {
                string in_juchu_no = model.Id.JuchuNo;
                short in_juchu_rec_no = model.Id.JuchuRecNo;
                int in_start_no = start_no;
                int in_end_no = end_no;
                string wk_etc_nowTimeYY = System.DateTime.Now.ToString("yy");
                string wk_jh_union_code = "";
                int wk_jh_branch_code = 0;
                string wk_jh_upd_union_code = "";
                int wk_jh_upd_branch_code = 0;
                int wk_jd_seal_qty = 0;
                string wk_jd_trader_reg_no = "";
                string wk_reg_date = System.DateTime.Now.ToString();
                string wk_upd_date = System.DateTime.Now.ToString();
                string wk_etc_start_countStr = "";
                string wk_etc_end_countStr = "";

                 SqlParameter[] sqlParameter = { };
                string sql1 = "SELECT  union_code,branch_code,upd_union_code,upd_branch_code FROM trn_juchu_h AS trn_juchu_h  WHERE juchu_no = '" + in_juchu_no + "'";
                SqlDataReader dataReader = db.GetDataCmd(sql1, sqlParameter);
                if (dataReader.Read())
                {
                    wk_jh_union_code = (string)dataReader["union_code"];
                    wk_jh_branch_code = (int)dataReader["branch_code"];
                    wk_jh_upd_union_code = (string)dataReader["upd_union_code"];
                    wk_jh_upd_branch_code = (int)dataReader["upd_branch_code"];
                }
                dataReader.Close();

                string sql2 = "SELECT seal_qty,trader_reg_no FROM trn_juchu_d AS trn_juchu_d WHERE juchu_no = '" + in_juchu_no + "' and juchu_rec_no = " + in_juchu_rec_no;
                dataReader = db.GetDataCmd(sql2, sqlParameter);
                if (dataReader.Read())
                {
                    wk_jd_seal_qty = (int)dataReader["seal_qty"];
                    wk_jd_trader_reg_no = (string)dataReader["trader_reg_no"];
                }
                dataReader.Close();
                string wk_union_code = wk_jh_union_code;
                int wk_branch_code = wk_jh_branch_code;
                string wk_trader_reg_no = wk_jd_trader_reg_no;
                string wk_juchu_no = in_juchu_no;
                int wk_report_qty = 0;
                string wk_purch_sts = "10";
                string wk_upd_union_code = wk_jh_upd_union_code;
                int wk_upd_branch_code = wk_jh_upd_branch_code;
                string wk_upd_func_id = "Insert_purch_proc";

                for (int count = in_start_no; count <= in_end_no; count++)
                {
                    Double number = count;
                    string autoNum = number.ToString("#######");
                    string wk_seal_no = wk_etc_nowTimeYY + autoNum;
                    int wk_seal_no_id = int.Parse(wk_seal_no);

                    string sqlStr = "INSERT INTO trn_purch_seal(" +
                                    "seal_no_id," +
                                    "seal_no," +
                                    "union_code," +
                                    "branch_code," +
                                    "trader_reg_no," +
                                    "juchu_no," +
                                    "report_qty," +
                                    "purch_sts," +
                                    "reg_date," +
                                    "upd_date," +
                                    "upd_union_code," +
                                    "upd_branch_code," +
                                    "upd_func_id" +
                            ") VALUES ( @wk_seal_no_id ,@wk_seal_no,@wk_union_code,@wk_branch_code,@wk_trader_reg_no ,@wk_juchu_no,@wk_report_qty,@wk_purch_sts,@wk_reg_date,@wk_upd_date,@wk_union_code,@wk_branch_code,@wk_upd_func_id )";

                    SqlParameter[] sqlParametertemp = {
                                                      new SqlParameter("@wk_seal_no_id",SqlDbType.Int), 
                                                      new SqlParameter("@wk_seal_no",SqlDbType.VarChar), 
                                                      new SqlParameter("@wk_union_code",SqlDbType.VarChar), 
                                                      new SqlParameter("@wk_branch_code",SqlDbType.SmallInt), 
                                                      new SqlParameter("@wk_trader_reg_no",SqlDbType.VarChar), 
                                                      new SqlParameter("@wk_juchu_no",SqlDbType.VarChar), 
                                                      new SqlParameter("@wk_report_qty",SqlDbType.Int), 
                                                      new SqlParameter("@wk_purch_sts",SqlDbType.VarChar), 
                                                      new SqlParameter("@wk_reg_date",SqlDbType.DateTime), 
                                                      new SqlParameter("@wk_upd_date",SqlDbType.DateTime), 
                                                      new SqlParameter("@wk_union_code",SqlDbType.VarChar), 
                                                      new SqlParameter("@wk_branch_code",SqlDbType.SmallInt), 
                                                      new SqlParameter("@wk_upd_func_id",SqlDbType.VarChar)
                                                      };
                    sqlParametertemp[0].Value=wk_seal_no_id; 
                    sqlParametertemp[1].Value=wk_seal_no;
                    sqlParametertemp[2].Value=wk_union_code; 
                    sqlParametertemp[3].Value=wk_branch_code; 
                    sqlParametertemp[4].Value=wk_trader_reg_no; 
                    sqlParametertemp[5].Value=wk_juchu_no; 
                    sqlParametertemp[6].Value=wk_report_qty; 
                    sqlParametertemp[7].Value=wk_purch_sts;
                    sqlParametertemp[8].Value=wk_reg_date;
                    sqlParametertemp[9].Value=wk_upd_date;
                    sqlParametertemp[10].Value=wk_union_code;
                    sqlParametertemp[11].Value=wk_branch_code;
                    sqlParametertemp[12].Value = wk_upd_func_id;
                    db.ExecuteCmd(sqlStr, sqlParametertemp);
                   
                    if (count == in_start_no)
                    {
                        wk_etc_start_countStr = wk_seal_no;
                    }
                    if (count == in_end_no)
                    {
                        wk_etc_end_countStr = wk_seal_no;
                    }


                }

                string sqlStr2 = "UPDATE trn_juchu_d  SET seal_str_no = '" + wk_etc_start_countStr + "',seal_end_no ='" + wk_etc_end_countStr + "'  WHERE juchu_no = '" + in_juchu_no + "'  and  juchu_rec_no = " + in_juchu_rec_no;
                db.ExecuteCmd(sqlStr2, sqlParameter);
                
            }catch(Exception ex1){
                LogManager.error("TRN_Purch_SealPersister insertByProcedure "+ex1.Message+ex1.StackTrace);
                return false;
            }

		    return true;
	    }
          */

        /**
         * 2004/04/01 T.okawa 追加
         * 組合員コードごとに一年間分の購入枚数をカウントする<br>
         * @param con コネクション
         * @param nen 集計対象年
         * @return Vector 報告率モデルを詰め込んだVector
         * @throws PersisterSQLException
         */
        public Hashtable getJuchuSealTotal(SqlDB con, String nen)
        {

            //TrnReportRate model = null;
            Hashtable hash = null;
            String pssql = null;

            try
            {
                pssql = "select union_code,count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no like @seal_no " + " and purch_sts <> '90' " + " group by union_code ";

                SqlParameter[] parmsU = new SqlParameter[]
            {
                  new SqlParameter("@seal_no", SqlDbType.VarChar,9) //
                 
            };
                parmsU[0].Value = nen.Substring(2) + "%";

                LogManager.debug("TRN_Purch_SealPersister + ### pssql : " + pssql);

                SqlDataReader rdr = con.GetLargeDataCmd(pssql, 60, parmsU);
                hash = new Hashtable();
                if (rdr != null)
                {
                    DataTable rs = DBUnit.ConvertDataReaderToDataTable(rdr);

                    foreach (DataRow row in rs.Rows)
                    {
                        hash.Add(DBUnit.GetString(row["union_code"]), DBUnit.GetString(row["cnt"]));

                    }
                }
            }
            catch (Exception e)
            {
                LogManager.error("TRN_Purch_SealPersister - getJuchuSealTotal" + e.ToString());
                throw e;
            }
            //finally
            //{
            //    try
            //    {
            //        con.Close();
            //    }
            //    catch (Exception e)
            //    {
            //        LogManager.fatal("TRN_Purch_SealPersister - getJuchuSealTotal - DB.close" + e.ToString());
            //    }

            //}

            return hash;
        }


        /**
         * シール番号のCOUNTを取得する。
         * @param con コネクション
         * @param purch_seal データモデル
         * @return int 未登録シールの枚数
         * @throws PersisterSQLException
         */
        public int getSealCount(SqlDB con, TrnPurchSeal_a purch_seal)
        {
            LogManager.info("TRN_Purch_SealPersister getSealCount in");
            TrnPurchSeal_a model = purch_seal;
            int count = 0;
            // <!-- 2003/8/5 T.Sakai add -->
            int ret = 0;

            // <!-- 2003/8/7 T.Sakai change 単一シール番号検索を複数シール検索へ変更 -->
            String pssql = "select count(seal_no) as cnt " + " from trn_purch_seal " + " where seal_no between @no1 and @no2";

            SqlParameter[] parmsU = new SqlParameter[] 
            {
                  new SqlParameter("@no1", SqlDbType.VarChar,9), //
                  new SqlParameter("@no2", SqlDbType.VarChar,9)//
            };
            parmsU[0].Value = model.SealStrNo;
            parmsU[1].Value = model.SealEndNo;
            // <!-- 2003/8/8 T.Sakai change 検索速度改善のため between .. and に戻す -->
            //String pssql = "select count(seal_no) as cnt "+
            //			" from trn_purch_seal "+
            //			" where "+
            //			" decimal(seal_no) >= ? "+
            //			" and decimal(seal_no) <= ? ";

            try
            {
                //SQL実行
                SqlDataReader rdr = con.GetDataCmd(pssql, parmsU);
                DataTable rs = DBUnit.ConvertDataReaderToDataTable(rdr);

                count = DBUnit.GetInt32(rs.Rows[0]["cnt"]);

                // <!-- 2003/8/5 T.Sakai add 差がゼロならOK -->
                ret = count - (int.Parse(model.SealEndNo) - int.Parse(model.SealStrNo) + 1);
                LogManager.info("TRN_Purch_SealPersister getSealCount ret " + ret);


            }
            catch (Exception e)
            {
                throw e;
            }
            LogManager.info("TRN_Purch_SealPersister getSealCount out");
            return ret;

        }

        /**
         * シール番号の報告件数を取得する。
         * @param con コネクション
         * @param purch_seal データモデル
         * @return int シール番号の報告件数
         * @throws PersisterSQLException
         */
        public int getSealReport(SqlDB con, TrnPurchSeal_a purch_seal)
        {
            LogManager.info("TRN_Purch_SealPersister getSealReport in");
            TrnPurchSeal_a model = purch_seal;
            int count = 0;
            // <!-- 2003/8/7 T.Sakai change 単一シール番号検索を複数シール検索へ変更 -->
            String pssql = "select count(seal_no) as cnt " +
                        " from trn_purch_seal " +
                        " where (seal_no between @no1 and @no2) " +
                        " and (report_qty>0)";
            SqlParameter[] parmsU = new SqlParameter[] 
            {
                  new SqlParameter("@no1", SqlDbType.VarChar,9), //
                  new SqlParameter("@no2", SqlDbType.VarChar,9)//
            };
            parmsU[0].Value = model.SealStrNo;
            parmsU[1].Value = model.SealEndNo;

            try
            {

                //SQL実行
                SqlDataReader rdr = con.GetDataCmd(pssql, parmsU);
                DataTable rs = DBUnit.ConvertDataReaderToDataTable(rdr);

                count = DBUnit.GetInt32(rs.Rows[0]["cnt"]);
                LogManager.info("TRN_Purch_SealPersister getSealReport count "+count);
            }
            catch (Exception e)
            {
               // throw e;
                LogManager.error("TRN_Purch_SealPersister getSealReport "+e.Message+e.StackTrace);
            }
            LogManager.info("TRN_Purch_SealPersister getSealReport out");

            return count;

        }

        /**
         * 確認シールを破棄する
         * @param con コネクション
         * @param purch_seal データモデル
         * @return int 確認シールを破棄数
         * @throws PersisterSQLException
         */
        public int hakiPurch_Seal(SqlDB con, TrnPurchSeal_a purch_seal)
        {
            LogManager.info("TRN_Purch_SealPersister hakiPurch_Seal in");
            TrnPurchSeal_a model = purch_seal;
            int ret = 0;

            String pssql = "update trn_purch_seal set " + "purch_sts = @purch_sts, " + "upd_date = @upd_date, " + "upd_union_code = @upd_union_code, " + "upd_branch_code = @upd_branch_code " + "where " + "seal_no between @no1 and @no2 ";
            SqlParameter[] parmsU = new SqlParameter[] 
            {
                 new SqlParameter("@purch_sts", SqlDbType.VarChar,2), //
                 new SqlParameter("@upd_date", SqlDbType.DateTime),//
                 new SqlParameter("@upd_union_code", SqlDbType.VarChar,5),//
                 new SqlParameter("@upd_branch_code", SqlDbType.SmallInt),//
                 new SqlParameter("@no1", SqlDbType.VarChar,9), //
                 new SqlParameter("@no2", SqlDbType.VarChar,9)//
            };
            parmsU[0].Value = TrnPurchSeal_a.STATUS_CANSEL;
            parmsU[1].Value = model.UpdDate;
            parmsU[2].Value = model.UpdUnionCode;
            parmsU[3].Value = model.UpdBranchCode;

            parmsU[4].Value = model.SealStrNo;
            parmsU[5].Value = model.SealEndNo;


            try
            {
                ret = con.ExecuteCmd(pssql, parmsU);

            }
            catch (Exception e)
            {
                LogManager.error("TRN_Purch_SealPersister hakiPurch_Seal "+e.Message+e.StackTrace);
                throw e;
            }
            LogManager.info("TRN_Purch_SealPersister hakiPurch_Seal over");
            return ret;
        }

        /**
         * 取消シールを破棄する
         * @param con コネクション
         * @param purch_seal データモデル
         * @return int 確認シールを破棄数
         * @throws PersisterSQLException
         */
        public int revoke_Seal(SqlDB con, TrnPurchSeal_a purch_seal)
        {
            LogManager.info("TRN_Purch_SealPersister revoke_Seal in");
            TrnPurchSeal_a model = purch_seal;
            int ret = 0;

            String pssql = "update trn_purch_seal set " + "purch_sts = @purch_sts, " + "upd_date = @upd_date, " + "upd_union_code = @upd_union_code, " + "upd_branch_code = @upd_branch_code " + "where " + "seal_no between @no1 and @no2 ";
            SqlParameter[] parmsU = new SqlParameter[] 
            {
                 new SqlParameter("@purch_sts", SqlDbType.VarChar,2), //
                  new SqlParameter("@upd_date", SqlDbType.DateTime),//
                   new SqlParameter("@upd_union_code", SqlDbType.VarChar,5),//
                    new SqlParameter("@upd_branch_code", SqlDbType.SmallInt),//

                  new SqlParameter("@no1", SqlDbType.VarChar,9), //
                  new SqlParameter("@no2", SqlDbType.VarChar,9)//
            };
            parmsU[0].Value = TrnPurchSeal_a.STATUS_NORMAL;
            parmsU[1].Value = model.UpdDate;
            parmsU[2].Value = model.UpdUnionCode;
            parmsU[3].Value = model.UpdBranchCode;

            parmsU[4].Value = model.SealStrNo;
            parmsU[5].Value = model.SealEndNo;


            try
            {
                ret = con.ExecuteCmd(pssql, parmsU);

            }
            catch (Exception e)
            {
                LogManager.error("TRN_Purch_SealPersister revoke_Seal " + e.Message + e.StackTrace);
                throw e;
            }
            LogManager.info("TRN_Purch_SealPersister revoke_Seal over");
            return ret;
        }

        /**
        * 指定された確認シール番号のデータモデルを取得する。
        * @param con コネクション
        * @param seal_no_start 確認シール番号
        * @return boolean
        * @throws PersisterSQLException
        */
        public bool existSealNo(SqlDB con, int seal_no_start)
        {
            return existSealNo(con, seal_no_start, -1);
        }

        /**
         * 指定された確認シール番号のデータモデルを取得する。
         * @param con コネクション
         * @param seal_no_start 確認シールstart番号
         * @param seal_no_end 確認シールend番号
         * @return boolean
         * @throws PersisterSQLException
         */
        public bool existSealNo(SqlDB con, int seal_no_start, int seal_no_end)
        {


            bool ret = false;

            String pssql = null;

            try
            {
                SqlParameter[] parmsU;
                if (seal_no_end > 0)
                {
                    // 2003/8/21 T.Sakai コメントアウト
                    // pssql = "select count(seal_no) as cnt "+
                    // 			"from trn_purch_seal "+
                    // 			"where "+
                    // 			" decimal(seal_no) >= ? "+
                    // 			" and decimal(seal_no) <= ? ";
                    // 2003/8/21 T.Sakai 追加
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no between @no1 and  @no2 ";
                    parmsU = new SqlParameter[] 
            {
                  new SqlParameter("@no1", SqlDbType.VarChar,9), //
                  new SqlParameter("@no2", SqlDbType.VarChar,9)//
            };
                    parmsU[0].Value = seal_no_start;
                    parmsU[1].Value = seal_no_end;

                }
                else
                {

                    // 2003/8/21 T.Sakai コメントアウト
                    // pssql = "select count(seal_no) as cnt "+
                    // 			"from trn_purch_seal "+
                    // 			"where "+
                    // 			" decimal(seal_no) = ? ";
                    // 2003/8/21 T.Sakai 追加
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no = @no1 ";
                    parmsU = new SqlParameter[] 
            {
                  new SqlParameter("@no1", SqlDbType.VarChar,9), //
                  
            };
                    parmsU[0].Value = seal_no_start;


                }

                LogManager.debug("TRN_Purch_SealPersister - #################### pssql : " + pssql);

                SqlDataReader rdr = con.GetDataCmd(pssql, parmsU);
                DataTable rs = DBUnit.ConvertDataReaderToDataTable(rdr);

                int count = 0;

                count = DBUnit.GetInt32(rs.Rows[0]["cnt"]);


                if (seal_no_end > 0)
                {
                    if (count == (seal_no_end - seal_no_start + 1))
                    {
                        ret = true;
                    }
                }
                else
                {
                    if (count == 1)
                    {
                        ret = true;
                    }
                }

            }
            catch (Exception e)
            {
                LogManager.error("TRN_Purch_SealPersister - existSealNo" + e.ToString());
                throw e;
            }
            //finally
            //{
            //    try
            //    {
            //        con.Close();
            //    }
            //    catch (Exception e)
            //    {
            //        LogManager.fatal("TRN_Purch_SealPersister - existSealNo - DB.close" + e.ToString());
            //    }

            //}

            return ret;

        }

        /**
         * 指定された確認シール番号が破棄されていないことを確認する
         * @param con コネクション
         * @param seal_no_start 確認シールstart番号
         * @param seal_no_end 確認シールend番号
         * @return int 範囲内で破棄されたシールの数
         * @throws PersisterSQLException
         */
        // <!-- 2003/8/8 T.Sakai change 検索速度改善のため引数をString化 -->
        //public int countCanselSealNo( Connection con,int seal_no_start , int seal_no_end )
        public int countCanselSealNo(SqlDB con, String seal_no_start, String seal_no_end)
        {


            int count = 0;

            String pssql = null;
            LogManager.debug("TRN_Purch_SealPersister - seal_no_start : " + seal_no_start);
            LogManager.debug("TRN_Purch_SealPersister - seal_no_end : " + seal_no_end);

            try
            {
                SqlParameter[] parmsU;
                // <!-- 2003/8/8 T.Sakai change 検索速度改善のため between .. and へ書き換え -->
                if (int.Parse(seal_no_end) > 0)
                {
                    // <!-- 2003/8/8 T.Sakai change 検索速度改善のため between .. and へ書き換え -->
                    //pssql = "select count(seal_no) as cnt "+
                    //			"from trn_purch_seal "+
                    //			"where "+
                    //			" decimal(seal_no) >= ? "+
                    //			" and decimal(seal_no) <= ? "+
                    //			" and purch_sts = ? " ;
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no between @no1 and @no2 " + " and purch_sts = @purch_sts ";
                    parmsU = new SqlParameter[] 
            {
                  new SqlParameter("@no1", SqlDbType.VarChar,9), //
                  new SqlParameter("@no2", SqlDbType.VarChar,9),//
                  new SqlParameter("@purch_sts", SqlDbType.VarChar,2)//
            };
                    parmsU[0].Value = getStrSealNo(int.Parse(seal_no_start));
                    parmsU[1].Value = getStrSealNo(int.Parse(seal_no_end));
                    parmsU[2].Value = TrnPurchSeal_a.STATUS_CANSEL;

                }
                else
                {

                    // <!-- 2003/8/8 T.Sakai change 検索速度改善のため between .. and へ書き換え -->
                    //pssql = "select count(seal_no) as cnt "+
                    //			"from trn_purch_seal "+
                    //			"where "+
                    //			" decimal(seal_no) = ? "+
                    //			" and purch_sts = ? " ;
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no = @no1 " + " and purch_sts = @purch_sts ";
                    parmsU = new SqlParameter[] 
            {
                  new SqlParameter("@no1", SqlDbType.VarChar,9), //
                    new SqlParameter("@purch_sts", SqlDbType.VarChar,2)//
            };
                    parmsU[0].Value = getStrSealNo(int.Parse(seal_no_start));
                    parmsU[1].Value = TrnPurchSeal_a.STATUS_CANSEL;
                }

                LogManager.debug("TRN_Purch_SealPersister - existCanselSealNo pssql : " + pssql);

                SqlDataReader rdr = con.GetDataCmd(pssql, parmsU);
                DataTable rs = DBUnit.ConvertDataReaderToDataTable(rdr);


                count = DBUnit.GetInt32(rs.Rows[0]["cnt"]);


            }
            catch (Exception e)
            {
                LogManager.error("TRN_Purch_SealPersister - existCanselSealNo" + e.ToString());
                throw e;
            }
            //finally
            //{
            //    try
            //    {
            //        con.Close();
            //    }
            //    catch (Exception e)
            //    {
            //        LogManager.fatal("TRN_Purch_SealPersister - existCanselSealNo - DB.close" + e.ToString());
            //    }
            //}

            return count;

        }

        /*
        public bool insertByProcedure(SqlDB con, TrnJuchud model, int start_no, int end_no)
        {

            LogManager.debug("TRN_Purch_SealPersister - insertByProcedure in --------------------------- ");
            LogManager.debug("TRN_Purch_SealPersister - start_no=" + start_no + " end_no=" + end_no);

            String procName = "Insert_trn_purch_seal_proc";
            String sql = "CALL " + procName + "( @p1, @p2, @p3, @p4, @p5, @p6 )";

            try
            {
                SqlParameter[] parmsU = new SqlParameter[] 
            {
                  new SqlParameter("@p1", SqlDbType.VarChar,10), //
                  new SqlParameter("@p2", SqlDbType.Int,3),//
                  new SqlParameter("@p3", SqlDbType.VarChar,9),//
                  new SqlParameter("@p4", SqlDbType.VarChar,9), //
                  new SqlParameter("@p5", SqlDbType.Int),//
                  new SqlParameter("@p6", SqlDbType.VarChar)//
            };
                parmsU[0].Value = model.Id.JuchuNo;
                parmsU[1].Value = model.Id.JuchuRecNo;
                parmsU[2].Value = start_no;
                parmsU[3].Value = end_no;
                parmsU[4].Value = 0;
                parmsU[5].Value = 0;


                con.ExecuteProcedure(sql, ref parmsU);



                int outErrorCode = DBUnit.GetInt32(parmsU[4].Value);
                String outErrorMsg = DBUnit.GetString(parmsU[5].Value);

                LogManager.debug("TRN_Purch_SealPersister - From Insert_trn_purch_seal_proc back sqlCode=" + outErrorCode + " returnString = [" + outErrorMsg + "]");
                if (outErrorCode != 0)
                {
                    LogManager.fatal("TRN_Purch_SealPersister - insert - CallableStatement close sql_code = " + outErrorCode + " returnString = [" + outErrorMsg + "]");
                    throw new Exception(" Insert_trn_purch_seal_proc error ");
                }

                LogManager.debug("TRN_Purch_SealPersister - insertByProcedure out --------------------------- ");
            }
            catch (Exception e)
            {
                throw e;
            }
            //finally
            //{
            //    try
            //    {
            //        con.Close();
            //    }
            //    catch (Exception e)
            //    {
            //        LogManager.fatal("TRN_Purch_SealPersister - insert - CallableStatement close " + e.ToString());
            //    }
            //}
            return true;
        }
         * */

        /**
         * シール番号が既に登録されているかどうかをチェック。
         * @param con コネクション
         * @param seal_no_start 確認シールstart番号
         * @param seal_no_end 確認シールend番号
         * @return int 報告件数が0ではないシール番号を含めていれば1を返す。
         * @throws PersisterSQLException
         */
        public int countDuplicationReport(SqlDB con, int seal_no_start, int seal_no_end)
        {

            int count = 0;
            String pssql = null;

            try
            {
                SqlParameter[] parmsU;
                if (seal_no_end > 0)
                {
                    // 2003/8/21 T.Sakai コメントアウト
                    // pssql = "select count(seal_no) as cnt "+
                    // 			"from trn_purch_seal "+
                    // 			"where "+
                    // 			" report_qty != 0 "+
                    // 			" and decimal(seal_no) >= ? "+
                    // 			" and decimal(seal_no) <= ? " ;
                    // 2003/8/21 T.Sakai 追加
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " report_qty != 0 " + " and seal_no between @no1 and @no2 ";

                    parmsU = new SqlParameter[] 
            {
                  new SqlParameter("@no1", SqlDbType.VarChar,9), //
                  new SqlParameter("@no2", SqlDbType.VarChar,9)//
            };
                    parmsU[0].Value = getStrSealNo(seal_no_start);
                    parmsU[1].Value = getStrSealNo(seal_no_end);



                }
                else
                {

                    // 2003/8/21 T.Sakai コメントアウト
                    // pssql = "select count(seal_no) as cnt "+
                    // 			"from trn_purch_seal "+
                    // 			"where "+
                    // 			" report_qty != 0 "+
                    // 			" and decimal(seal_no) = ? ";
                    // 2003/8/21 T.Sakai 追加
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " report_qty != 0 " + " and seal_no = @no1 ";

                    parmsU = new SqlParameter[] 
            {
                  new SqlParameter("@no1", SqlDbType.VarChar,9) //
            };

                    parmsU[0].Value = getStrSealNo(seal_no_start);
                }

                LogManager.debug("TRN_Purch_SealPersister - ### pssql : " + pssql);

                SqlDataReader rdr = con.GetDataCmd(pssql, parmsU);
                DataTable rs = DBUnit.ConvertDataReaderToDataTable(rdr);


                count = DBUnit.GetInt32(rs.Rows[0]["cnt"]);


                LogManager.debug("TRN_Purch_SealPersister - ### count : " + count);

            }
            catch (Exception e)
            {
                LogManager.error("TRN_Purch_SealPersister - countDuplicationReport" + e.ToString());
                throw e;
            }
            //finally
            //{
            //    try
            //    {
            //        con.Close();
            //    }
            //    catch (Exception e)
            //    {
            //        LogManager.fatal("TRN_Purch_SealPersister - countDuplicationReport - DB.close" + e.ToString());
            //    }

            //}

            return count;

        }

        /**
         * シールの購入者と報告者が異なるかどうかをチェック。<br>
         * 渡されたシール番号について他の組合員コードが存在すると購入者と報告者が異なると判断。
         * @param con コネクション
         * @param seal_no_start 確認シールstart番号
         * @param seal_no_end 確認シールend番号
         * @param union_code 組合員コード
         * @return int 渡された条件による組合員コードの数
         * @throws PersisterSQLException
         */
        public int countDifferentUnion(SqlDB con, int seal_no_start, int seal_no_end, String union_code)
        {


            int count = 0;
            String pssql = null;

            try
            {
                SqlParameter[] parmsU;
                if (seal_no_end > 0)
                {
                    // 2003/8/21 T.Sakai コメントアウト
                    // pssql = "select count(seal_no) as cnt "+
                    // 			"from trn_purch_seal "+
                    // 			"where "+
                    // 			" decimal(seal_no) >= ? "+
                    // 			" and decimal(seal_no) <= ? "+
                    // 			" and union_code != ? " ;
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no between @no1 and @no2 " + " and union_code != @union_code ";

                    parmsU = new SqlParameter[] 
            {
                  new SqlParameter("@no1", SqlDbType.VarChar,9), //
                  new SqlParameter("@no2", SqlDbType.VarChar,9),//
                  new SqlParameter("@purch_sts", SqlDbType.VarChar,5)//
            };
                    parmsU[0].Value = getStrSealNo(seal_no_start);
                    parmsU[1].Value = getStrSealNo(seal_no_end);
                    parmsU[2].Value = union_code;

                }
                else
                {

                    // 2003/8/21 T.Sakai コメントアウト
                    // pssql = "select count(seal_no) as cnt "+
                    // 			"from trn_purch_seal "+
                    // 			"where "+
                    // 			" decimal(seal_no) = ? "+
                    // 			" and union_code != ? ";
                    // 2003/8/21 T.Sakai 追加
                    pssql = "select count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no = @no1 " + " and union_code != @union_code ";

                    parmsU = new SqlParameter[] 
            {
                  new SqlParameter("@no1", SqlDbType.VarChar,9), //
                  new SqlParameter("@purch_sts", SqlDbType.VarChar,5)//
            };
                    parmsU[0].Value = getStrSealNo(seal_no_start);
                    parmsU[1].Value = union_code;
                }

                LogManager.debug("TRN_Purch_SealPersister - ### pssql : " + pssql);

                SqlDataReader rdr = con.GetDataCmd(pssql, parmsU);
                DataTable rs = DBUnit.ConvertDataReaderToDataTable(rdr);


                count = DBUnit.GetInt32(rs.Rows[0]["cnt"]);


                LogManager.debug("TRN_Purch_SealPersister - ### count : " + count);

            }
            catch (Exception e)
            {
                LogManager.error("TRN_Purch_SealPersister - countDifferentUnion" + e.ToString());
                throw e;
            }
            //finally
            //{
            //    try
            //    {
            //        con.Close();
            //    }
            //    catch (Exception e)
            //    {
            //        LogManager.fatal("TRN_Purch_SealPersister - countDifferentUnion - DB.close" + e.ToString());
            //    }

            //}

            return count;
        }


        /**
         * 2004/04/01 T.okawa 追加
         * 組合員コードごとに一年間分の購入枚数をカウントする<br>
         * @param con コネクション
         * @param nen 集計対象年
         * @return Vector 報告率モデルを詰め込んだVector
         * @throws PersisterSQLException
         */
        /*
        public Hashtable getJuchuSealTotal(SqlDB con, String nen)
        {

            //TrnReportRate model = null;
            Hashtable hash = null;
            String pssql = null;

            try
            {
                pssql = "select union_code,count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no like @seal_no " + " and purch_sts = '10' " + " group by union_code ";

                SqlParameter[] parmsU = new SqlParameter[]
            {
                  new SqlParameter("@seal_no", SqlDbType.VarChar,9) //
                 
            };
                parmsU[0].Value = nen.Substring(2) + "%";

                LogManager.debug("TRN_Purch_SealPersister + ### pssql : " + pssql);

                SqlDataReader rdr = con.GetDataCmd(pssql, parmsU);
                hash = new Hashtable();
                if (rdr != null)
                {
                    DataTable rs = DBUnit.ConvertDataReaderToDataTable(rdr);

                    foreach (DataRow row in rs.Rows)
                    {
                        hash.Add(DBUnit.GetString(row["union_code"]), DBUnit.GetString(row["cnt"]));

                    }
                }
            }
            catch (Exception e)
            {
                LogManager.error("TRN_Purch_SealPersister - getJuchuSealTotal" + e.ToString());
                throw e;
            }
            //finally
            //{
            //    try
            //    {
            //        con.Close();
            //    }
            //    catch (Exception e)
            //    {
            //        LogManager.fatal("TRN_Purch_SealPersister - getJuchuSealTotal - DB.close" + e.ToString());
            //    }

            //}

            return hash;
        }
        */
        /**
         * 2004/04/01 T.okawa 追加
         * 組合員コードごとに一年間分の報告枚数をカウントする<br>
         * @param con コネクション
         * @param nen 集約対象年
         * @return Hashtable 報告枚数 
         * @throws PersisterSQLException
         */
        public Hashtable getReportSealTotal(SqlDB db, String nen)
        {
            String pssql = null;
            Hashtable hash = null;

            try
            {
                pssql = "select union_code,count(seal_no) as cnt " + "from trn_purch_seal " + "where " + " seal_no like @seal_no " + " and purch_sts = '10' " + " and report_qty > 0 " + " group by union_code ";

                SqlParameter[] parmsU = new SqlParameter[]
            {
                  new SqlParameter("@seal_no", SqlDbType.VarChar,9) //
                 
            };
                parmsU[0].Value = nen.Substring(2) + "%";

                LogManager.debug("TRN_Purch_SealPersister - ### pssql : " + pssql);

                SqlDataReader rdr = db.GetDataCmd(pssql, parmsU);

                hash = new Hashtable();
                if (rdr != null)
                {
                    DataTable rs = DBUnit.ConvertDataReaderToDataTable(rdr);

                    foreach (DataRow row in rs.Rows)
                    {
                        hash.Add(DBUnit.GetString(row["union_code"]), DBUnit.GetString(row["cnt"]));
                    }
                }
            }
            catch (Exception e)
            {
                LogManager.error("TRN_Purch_SealPersister - getReportSealTotal" + e.ToString());
                throw e;
            }
            //finally
            //{
            //    try
            //    {
            //        con.Close();
            //    }
            //    catch (Exception e)
            //    {
            //        LogManager.fatal("TRN_Purch_SealPersister - getReportSealTotal - DB.close" + e.ToString());
            //    }

            //}
            return hash;
        }

        public bool updatetReportSealSts(SqlDB db, string seal_no_start, string seal_no_end, string sts)
        {
            if (StringUtil.isEmpty(seal_no_end))
            {
                seal_no_end = seal_no_start;
            }
            string sql = "update trn_purch_seal set purch_sts = @purch_sts where  seal_no between @no1 and @no2 ";
            try
            {

                SqlParameter[] sqlParameter = {          
new SqlParameter("@purch_sts",SqlDbType.VarChar),       
new SqlParameter("@no1" ,SqlDbType.VarChar),       
new SqlParameter("@no2" ,SqlDbType.VarChar)
 };

                sqlParameter[0].Value = sts;
                sqlParameter[1].Value = seal_no_start;
                sqlParameter[2].Value = seal_no_end;
               
                db.ExecuteCmd(sql, sqlParameter);

            }
            catch (Exception e)
            {
                LogManager.error(" trn_purch_seal_persister updatetReportSealSts " + e.Message + e.StackTrace);
                throw new Exception("trn_purch_seal_persister updatetReportSealSts  error");
                // return false;
            }

            return true;
        }
    }
}
